﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.ComponentModel;
using System.Data.OracleClient;

namespace Flockmaster
{
    public class Oracle : DatabaseCopy
    {
        public Oracle()
        {
            dest_Type = DBTypes.Oracle;
        }

        public override bool DoesTableExist(string TableName, DBTypes m_dbType)
        {
            return base.DoesTableExist(TableName, m_dbType);
        }

        public override void DropTable(string TableName)
        {
            base.DropTable(TableName);
        }

        public override void CopyTable(SendCmd Message)
        {
            base.CopyTable(Message);
        }

        protected override void BackWorker_ProgressChanged(object sender, ProgressChangedEventArgs e)
        {
            base.BackWorker_ProgressChanged(sender, e);
        }

        protected override void BackWorker_RunWorkerCompleted(object sender, RunWorkerCompletedEventArgs e)
        {
            base.BackWorker_RunWorkerCompleted(sender, e);
        }

        protected override void BackWorker_DoWork(object sender, System.ComponentModel.DoWorkEventArgs e)
        {
            SendCmd Message;
            try
            {
                Message = ((SendCmd)e.Argument);
            }
            catch
            {
                throw new Exception("调用复制函数时，提供的参数格式不符，请检查！");
                return;
            }
            string TableName = Message.TableName.ToString();
            string query = Message.SqlCmd;
            string field, param;
            bool IsKey = false;
            try
            {
                using (IDbCommand sqlCmd = this.srcConnection.CreateCommand())
                {
                    sqlCmd.CommandText = query;
                    IDataReader srcReader = sqlCmd.ExecuteReader(CommandBehavior.KeyInfo);
                    DataTable scheamTable = srcReader.GetSchemaTable();

                    // 创建 SQL Server Compact Edition 表结构
                    using (IDbCommand destCmd = this.destConnection.CreateCommand())
                    {
                        string cmd = GenerateTableSchemaSql(scheamTable,ref IsKey);
                        if (cmd == String.Empty)
                        {
                            return;
                        }
                        destCmd.CommandText = cmd;
                        destCmd.ExecuteNonQuery();
                    }

                    // 生成 SQL Server Compact Edition 数据插入 SQL 语句
                    StringBuilder sbFields = new StringBuilder();
                    StringBuilder sbParams = new StringBuilder();


                    using (OracleCommand destCommand = (OracleCommand)destConnection.CreateCommand())
                    {
                        if (!Message.IsOnlyTableStructure)
                        {
                            DataRow schemaRow;
                            for (int i = 0; i < scheamTable.Rows.Count; i++)
                            {
                                if (i != 0)
                                {
                                    sbFields.Append(", ");
                                    sbParams.Append(", ");
                                }

                                schemaRow = scheamTable.Rows[i];
                                field = string.Format("{0}", schemaRow["ColumnName"]); //字段名称
                                param = ":" + schemaRow["ColumnName"].ToString(); //参数名称
                                sbFields.Append(field);
                                sbParams.Append(param);
                                destCommand.Parameters.AddWithValue(param, null);
                            }

                            string insertSql = string.Format("INSERT INTO \"{0}\"({1}) VALUES({2})", TableName.ToUpper(), sbFields, sbParams);
                            destCommand.CommandText = insertSql;

                            //            Debug.WriteLine(insertSql);
                            count = 0;
                            int errorcount = 0;
                            // 执行数据导入
                            object[] values = new object[scheamTable.Rows.Count];
                            while (srcReader.Read())
                            {
                                //values = new object[srcReader.FieldCount];
                                try
                                {
                                    srcReader.GetValues(values);
                                }
                                catch (Exception ex)
                                {
                                    BackWorker.ReportProgress((int)MessageTypes.ErrorMessage, "从" + TableName + "读取数据时,发生" + ex.Message);
                                    if (errorcount < 200)
                                    {
                                        continue;
                                    }
                                    else if (errorcount == 200)
                                    {
                                        BackWorker.ReportProgress((int)MessageTypes.ErrorMessage, "从" + TableName + "读取数据时,发生了太多的错误，可能是数据连接已经断开，或者其他未知原因，程序中断了该表的复制！");
                                        break;
                                    }
                                    errorcount++;
                                }
                                for (int i = 0; i < values.Length; i++)
                                {
                                    destCommand.Parameters[i].Value = values[i];
                                }
                                try
                                {
                                    destCommand.ExecuteNonQuery();
                                }
                                catch (Exception ex)
                                {
                                    string value = "";
                                    for (int j = 0; j < values.Length; j++)
                                    {
                                        if (j != 0)
                                        {
                                            value += ";";
                                        }
                                        value += values[j].ToString();

                                    }
                                    BackWorker.ReportProgress((int)MessageTypes.ErrorMessage, "复制表" + TableName + "时,发生" + ex.Message + "\r\n" + value);
                                    if (errorcount < 200)
                                    {
                                        continue;
                                    }
                                    else if (errorcount == 200)
                                    {
                                        BackWorker.ReportProgress((int)MessageTypes.ErrorMessage, "从" + TableName + "读取数据时,发生了太多的错误，可能是数据连接已经断开，或者其他未知原因，程序中断了该表的复制！");
                                        break;
                                    }
                                    errorcount++;
                                }
                                if (!ISStop)
                                {
                                    break;
                                }

                                count++;
                                BackWorker.ReportProgress(count);
                            }
                        }
                        srcReader.Close();
                        srcReader.Dispose();
                    }
                    ISStop = true;
                }
            }
            catch (Exception ex)
            {
                BackWorker.ReportProgress((int)MessageTypes.ErrorMessage, "复制表" + TableName + "时,发生" + ex.Message);
            }
        }

        /// <summary>
        /// 生成创建数据库表结构的 SQLCE 语句。*******************
        /// </summary>
        protected override string GenerateTableSchemaSql(DataTable schemaTable, ref bool IsKey)
        {
            StringBuilder tableSql = new StringBuilder();

            /* 生成创建表定义语句 */
            string tableName = schemaTable.Rows[0]["BaseTableName"].ToString();
            tableSql.Append("CREATE TABLE \"").Append(tableName.ToUpper()).AppendLine("\" (");

            /* 生成各列的定义语句 */
            string columnName, allowDBNull, columnType;
            columnName = String.Empty;
            DataRow row;
            StringBuilder sbPKFields = new StringBuilder();
            try
            {
                for (int i = 0; i < schemaTable.Rows.Count; i++)
                {
                    if (i != 0) tableSql.AppendLine(",");

                    row = schemaTable.Rows[i];
                    columnName = (string)row["ColumnName"];
                    allowDBNull = ((bool)row["AllowDBNull"] == true ? "" : "NOT NULL");

                    if ((bool)row["IsKey"])
                    {
                        sbPKFields.AppendFormat("{0},", columnName);
                        IsKey = true;
                    }
                    columnType = GetDataType(schemaTable, i);
                    tableSql.AppendFormat("  {0} {1} {2}", columnName, columnType, allowDBNull);
                }

                /* 生成主键约束语句 */
                //if (IsKey)
                //{
                //    string pkFields = sbPKFields.ToString().TrimEnd(',');
                //    tableSql.AppendLine(",");
                //    tableSql.Append("  CONSTRAINT PK_").Append(tableName.Replace(" ", "_")).Append(" PRIMARY KEY(").Append(pkFields).AppendLine(")");
                //}
                tableSql.AppendLine(")");
            }
            catch (Exception ex)
            {
                BackWorker.ReportProgress((int)MessageTypes.ErrorMessage, "表" + tableName + "中" + columnName + "字段" + ex.Message);
                return String.Empty;
            }
            return tableSql.ToString();
        }

        /// <summary>
        /// 抓取目标数据库里字段类型***************
        /// </summary>
        /// <param name="schemaTable"></param>
        /// <param name="i"></param>
        /// <returns></returns>
        protected override string GetDataType(DataTable scheamTable, int i)
        {
            string dataType = GetNativeType(scheamTable.Rows[i]["DataType"].ToString());
            Int32 NumericPrecision = -2;
            if (scheamTable.Rows[i]["NumericPrecision"] != null && scheamTable.Rows[i]["NumericPrecision"].ToString() != "")
            {
                NumericPrecision = Convert.ToInt32(scheamTable.Rows[i]["NumericPrecision"].ToString());
            }
            try
            {
                Int32 colsize = (Int32)scheamTable.Rows[i]["ColumnSize"];
                //Int32 numericScale = (Int32)scheamTable.Rows[i]["NumericScale"];
                switch (dataType)
                {
                    case "BFILE":
                        break;
                    case "DATE":
                        break;
                    case "NUMBER":
                        if (Convert.ToInt32(NumericPrecision) <= 127)
                        {
                            dataType = string.Format("{0}({1},{2})", dataType, colsize + NumericPrecision*2, NumericPrecision);
                        }
                        else
                        {
                            dataType = string.Format("{0}({1})", dataType, colsize);
                        }
                        break;
                    case "NVARCHAR2":
                        if (colsize > 2147483647) throw new Exception("超出Oracle最大取值范围");
                        if (colsize == 3950)
                        {
                            dataType = "UROWID";
                        }
                        else if (colsize > 3950 && colsize <= 2147483647)
                        {
                            dataType = "LONG";
                        }
                        else
                        {
                            dataType = string.Format("{0}({1})", dataType, colsize);
                        }
                        break;
                    default:
                        break;
                }
            }
            catch (Exception ex)
            {
                BackWorker.ReportProgress((int)MessageTypes.ErrorMessage, "在进行类型转换时发生异常！具体错误信息如下：\r\n" + ex.Message);
            }
            return dataType;
        }

        /// <summary>
        /// 从 .NET 数据类型获取对应的 SqlSC  类型名称。***********
        /// </summary>
        protected override string GetNativeType(string typeName)
        {
            switch (typeName)
            {
                case "System.Byte[]":
                    return "BFILE";
                case "System.DateTime":
                    return "DATE";
                case "System.Decimal":
                    return "NUMBER";
                case "System.String":
                    return "NVARCHAR2";
                case "System.Int32":
                    return "NUMBER";
                case "System.Int64":
                    return "NUMBER";
                case "System.Int16":
                    return "NUMBER";
                case "System.Double":
                    return "NUMBER";
                case "System.Boolean":
                    return "NUMBER";
                default:
                    throw new ApplicationException(string.Format("{0}类型数据不被Oracle所支持，请更改由此可能造成的问题！。", typeName));
            }
        }

    }
}
